{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "# 加载base_info和reg\n",
    "base_data = pd.read_csv('./data/baseinfo.csv',encoding='utf-8')\n",
    "reg_data = pd.read_csv('./data/reginfo_extract3(modify).csv',encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_id</th>\n",
       "      <th>credit_code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>91110105801754689Q</td>\n",
       "      <td>北京真维嘉真空设备有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1017</td>\n",
       "      <td>911101056804953000</td>\n",
       "      <td>北京鸿福兴达装饰工程有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1065</td>\n",
       "      <td>911101087501253000</td>\n",
       "      <td>北京臻睿胜科技有限责任公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1172</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1258</td>\n",
       "      <td>91110107560415108J</td>\n",
       "      <td>北京华特鑫业科贸有限公司</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id          credit_code            name\n",
       "0        1000   91110105801754689Q   北京真维嘉真空设备有限公司\n",
       "1        1017  911101056804953000   北京鸿福兴达装饰工程有限公司\n",
       "2        1065  911101087501253000    北京臻睿胜科技有限责任公司\n",
       "3        1172   91110108754157151K  北京真彩科创电子技术有限公司\n",
       "4        1258   91110107560415108J    北京华特鑫业科贸有限公司"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_id</th>\n",
       "      <th>author Nationality</th>\n",
       "      <th>fullname</th>\n",
       "      <th>regnum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>AUTOSCREEN拼接单元遥控器软件</td>\n",
       "      <td>2011SR044451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>RClink express网络抓屏软件</td>\n",
       "      <td>2011SR044453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>REALCOLOR虚屏软件</td>\n",
       "      <td>2011SR044389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>REALCOLOR集中管理软件</td>\n",
       "      <td>2011SR044449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>WALLCONTROAL大屏拼接管理软件</td>\n",
       "      <td>2011SR044565</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id author Nationality              fullname        regnum\n",
       "0        1172     北京真彩科创电子技术有限公司   AUTOSCREEN拼接单元遥控器软件  2011SR044451\n",
       "1        1172     北京真彩科创电子技术有限公司  RClink express网络抓屏软件  2011SR044453\n",
       "2        1172     北京真彩科创电子技术有限公司         REALCOLOR虚屏软件  2011SR044389\n",
       "3        1172     北京真彩科创电子技术有限公司       REALCOLOR集中管理软件  2011SR044449\n",
       "4        1172     北京真彩科创电子技术有限公司  WALLCONTROAL大屏拼接管理软件  2011SR044565"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reg_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#reg_data的author_Nationality 去除符号\n",
    "def preprocess_title(title):\n",
    "    title = title.lower()\n",
    "    title = title.replace(',', ' ')\n",
    "    title = title.replace(\"'\", '')\n",
    "    title = title.replace(\"\\\"\",'')#增加 移除双引号\n",
    "    title = title.replace('(','')\n",
    "    title = title.replace(')','')\n",
    "    title = title.replace('（','')\n",
    "    title = title.replace('）','')\n",
    "    title = title.replace('&', 'and')\n",
    "    title = title.replace(\";\", '')\n",
    "    title = title.replace(' ','')\n",
    "    #title = title.encode('utf-8', 'ignore')\n",
    "    return title.strip()\n",
    "\n",
    "reg_data['nor_authors'] = reg_data['author Nationality'].map(preprocess_title)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "base_data['nor_names'] = base_data['name'].map(preprocess_title)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4685, 9)"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#企业基础数据company_id\tcredit_code\tname；\n",
    "#企业软著数据company_id\tauthor Nationality\tfullname\tregnum\n",
    "#根据标准化之后的名字进行连接\n",
    "data_attempt1 = pd.merge(base_data, reg_data, how='inner', left_on=['nor_names'],\n",
    "                         right_on=['nor_authors'])\n",
    "data_attempt1.shape\n",
    "#data_attempt1.to_csv(\"data/data_attempt1.csv\",sep=',',encoding=\"utf-8\",index = False)\n",
    "#data_attempt1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_id_x</th>\n",
       "      <th>credit_code</th>\n",
       "      <th>name</th>\n",
       "      <th>nor_names</th>\n",
       "      <th>company_id_y</th>\n",
       "      <th>author Nationality</th>\n",
       "      <th>fullname</th>\n",
       "      <th>regnum</th>\n",
       "      <th>nor_authors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1172</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>AUTOSCREEN拼接单元遥控器软件</td>\n",
       "      <td>2011SR044451</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1172</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>RClink express网络抓屏软件</td>\n",
       "      <td>2011SR044453</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1172</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>REALCOLOR虚屏软件</td>\n",
       "      <td>2011SR044389</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1172</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>REALCOLOR集中管理软件</td>\n",
       "      <td>2011SR044449</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1172</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>WALLCONTROAL大屏拼接管理软件</td>\n",
       "      <td>2011SR044565</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id_x         credit_code            name       nor_names  \\\n",
       "0          1172  91110108754157151K  北京真彩科创电子技术有限公司  北京真彩科创电子技术有限公司   \n",
       "1          1172  91110108754157151K  北京真彩科创电子技术有限公司  北京真彩科创电子技术有限公司   \n",
       "2          1172  91110108754157151K  北京真彩科创电子技术有限公司  北京真彩科创电子技术有限公司   \n",
       "3          1172  91110108754157151K  北京真彩科创电子技术有限公司  北京真彩科创电子技术有限公司   \n",
       "4          1172  91110108754157151K  北京真彩科创电子技术有限公司  北京真彩科创电子技术有限公司   \n",
       "\n",
       "   company_id_y author Nationality              fullname        regnum  \\\n",
       "0          1172     北京真彩科创电子技术有限公司   AUTOSCREEN拼接单元遥控器软件  2011SR044451   \n",
       "1          1172     北京真彩科创电子技术有限公司  RClink express网络抓屏软件  2011SR044453   \n",
       "2          1172     北京真彩科创电子技术有限公司         REALCOLOR虚屏软件  2011SR044389   \n",
       "3          1172     北京真彩科创电子技术有限公司       REALCOLOR集中管理软件  2011SR044449   \n",
       "4          1172     北京真彩科创电子技术有限公司  WALLCONTROAL大屏拼接管理软件  2011SR044565   \n",
       "\n",
       "      nor_authors  \n",
       "0  北京真彩科创电子技术有限公司  \n",
       "1  北京真彩科创电子技术有限公司  \n",
       "2  北京真彩科创电子技术有限公司  \n",
       "3  北京真彩科创电子技术有限公司  \n",
       "4  北京真彩科创电子技术有限公司  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#data_attempt1.to_csv(\"data/data_attempt1(modify).csv\",sep=',',encoding=\"utf-8\",index = False)\n",
    "data_attempt1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "0% [##############################] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:02\n"
     ]
    }
   ],
   "source": [
    "#以两个表格中的企业名称进行 编辑距离连接\n",
    "import py_stringsimjoin as ssj\n",
    "import py_stringmatching as sm\n",
    "\n",
    "base_data['id'] = range(base_data.shape[0])\n",
    "reg_data['id'] = range(reg_data.shape[0])\n",
    "data_attempt2 = ssj.edit_distance_join(base_data, reg_data, 'id', 'id', 'nor_names',\n",
    "                                        'nor_authors', l_out_attrs=['nor_names', 'credit_code'],\n",
    "                                         r_out_attrs=['nor_authors', 'fullname','regnum'], threshold=2)#Input is expected to be a string\n",
    "# selecting the entries that have the same production year\n",
    "\n",
    "#data_attempt2.to_csv(\"data/data_attempt2.csv\",sep=',',encoding=\"utf-8\",index = False)\n",
    "#data_attempt2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4738, 9)"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#data_attempt2.to_csv(\"data/data_attempt2(modify).csv\",sep=',',encoding=\"utf-8\",index = False)\n",
    "data_attempt2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Magellan\n",
    "#Substep A: Finding a candidate set (Blocking)\n",
    "# transforming the \"budget\" column into string and creating a new **mixture** column\n",
    "ssj.utils.converter.dataframe_column_to_str(base_data, 'company_id', inplace=True)\n",
    "base_data['mixture'] = base_data['nor_names']\n",
    "#base_data['mixture'] = base_data['nor_names']\n",
    "# repeating the same thing for the Kaggle dataset\n",
    "ssj.utils.converter.dataframe_column_to_str(reg_data, 'company_id', inplace=True)\n",
    "reg_data['mixture'] = reg_data['nor_authors']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "0% [##############################] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:01:07\n"
     ]
    }
   ],
   "source": [
    "C1 = ssj.overlap_coefficient_join(base_data, reg_data, 'id', 'id', 'mixture', 'mixture',sm.QgramTokenizer(), \n",
    "                                 l_out_attrs=['company_id','nor_names', 'credit_code'],\n",
    "                                 r_out_attrs=['company_id','nor_authors', 'fullname','regnum'],\n",
    "                                 threshold=0.80)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9116, 11)"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#C1.to_csv(\"data/C（0.78）.csv\",sep=',',encoding=\"utf-8\",index = False)\n",
    "C1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "C1.to_csv(\"data/C.csv\",sep=',',encoding=\"utf-8\",index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import py_entitymatching as em\n",
    "em.set_key(base_data, 'id')   # specifying the key column in the kaggle dataset\n",
    "em.set_key(reg_data, 'id')     # specifying the key column in the imdb dataset\n",
    "em.set_key(C1, '_id')            # specifying the key in the candidate set\n",
    "em.set_ltable(C1, base_data)   # specifying the left table \n",
    "em.set_rtable(C1, reg_data)     # specifying the right table\n",
    "em.set_fk_rtable(C1, 'r_id')     # specifying the column that matches the key in the right table \n",
    "em.set_fk_ltable(C1, 'l_id')     # specifying the column that matches the key in the left table \n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Sampling 500 pairs and writing this sample into a .csv file\n",
    "sampled = C1.sample(500, random_state=0)\n",
    "sampled.to_csv('./data/sampled(modify).csv', encoding='utf-8')\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Metadata file is not present in the given path; proceeding to read the csv file.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>_id</th>\n",
       "      <th>l_id</th>\n",
       "      <th>r_id</th>\n",
       "      <th>l_company_id</th>\n",
       "      <th>l_nor_names</th>\n",
       "      <th>l_credit_code</th>\n",
       "      <th>r_company_id</th>\n",
       "      <th>r_nor_authors</th>\n",
       "      <th>r_fullname</th>\n",
       "      <th>r_regnum</th>\n",
       "      <th>_sim_score</th>\n",
       "      <th>label</th>\n",
       "      <th>Unnamed: 13</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6520</td>\n",
       "      <td>6520</td>\n",
       "      <td>8000</td>\n",
       "      <td>3609</td>\n",
       "      <td>516739</td>\n",
       "      <td>清华大学</td>\n",
       "      <td>\\N</td>\n",
       "      <td>516739</td>\n",
       "      <td>清华大学</td>\n",
       "      <td>大数据构件辅助选型及参数配置工具软件</td>\n",
       "      <td>2016SR129812</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2029</td>\n",
       "      <td>2029</td>\n",
       "      <td>1416</td>\n",
       "      <td>1038</td>\n",
       "      <td>91355</td>\n",
       "      <td>中国科学技术信息研究所</td>\n",
       "      <td>\\N</td>\n",
       "      <td>91355</td>\n",
       "      <td>中国科学技术信息研究所</td>\n",
       "      <td>用户行为分析软件</td>\n",
       "      <td>2015SR273097</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1940</td>\n",
       "      <td>1940</td>\n",
       "      <td>1416</td>\n",
       "      <td>949</td>\n",
       "      <td>91355</td>\n",
       "      <td>中国科学技术信息研究所</td>\n",
       "      <td>\\N</td>\n",
       "      <td>91355</td>\n",
       "      <td>中国科学技术信息研究所</td>\n",
       "      <td>会议文献规范质检系统</td>\n",
       "      <td>2014SR210008</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6070</td>\n",
       "      <td>6070</td>\n",
       "      <td>7234</td>\n",
       "      <td>3298</td>\n",
       "      <td>466719</td>\n",
       "      <td>北京赛伟网络技术有限责任公司</td>\n",
       "      <td>91110116102603515K</td>\n",
       "      <td>466719</td>\n",
       "      <td>北京赛伟网络技术有限责任公司</td>\n",
       "      <td>能源领域数据通信专用网规划与优化软件</td>\n",
       "      <td>2011SR065311</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5006</td>\n",
       "      <td>5006</td>\n",
       "      <td>3973</td>\n",
       "      <td>2306</td>\n",
       "      <td>255515</td>\n",
       "      <td>北京讯威安通无线通信技术有限公司</td>\n",
       "      <td>91110108753305255F</td>\n",
       "      <td>255515</td>\n",
       "      <td>北京讯威安通无线通信技术有限公司</td>\n",
       "      <td>视语音对讲系统</td>\n",
       "      <td>2014SR054225</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0   _id  l_id  r_id  l_company_id       l_nor_names  \\\n",
       "0        6520  6520  8000  3609        516739              清华大学   \n",
       "1        2029  2029  1416  1038         91355       中国科学技术信息研究所   \n",
       "2        1940  1940  1416   949         91355       中国科学技术信息研究所   \n",
       "3        6070  6070  7234  3298        466719    北京赛伟网络技术有限责任公司   \n",
       "4        5006  5006  3973  2306        255515  北京讯威安通无线通信技术有限公司   \n",
       "\n",
       "        l_credit_code  r_company_id     r_nor_authors          r_fullname  \\\n",
       "0                  \\N        516739              清华大学  大数据构件辅助选型及参数配置工具软件   \n",
       "1                  \\N         91355       中国科学技术信息研究所            用户行为分析软件   \n",
       "2                  \\N         91355       中国科学技术信息研究所          会议文献规范质检系统   \n",
       "3  91110116102603515K        466719    北京赛伟网络技术有限责任公司  能源领域数据通信专用网规划与优化软件   \n",
       "4  91110108753305255F        255515  北京讯威安通无线通信技术有限公司             视语音对讲系统   \n",
       "\n",
       "       r_regnum  _sim_score  label  Unnamed: 13  \n",
       "0  2016SR129812         1.0      1            0  \n",
       "1  2015SR273097         1.0      1            0  \n",
       "2  2014SR210008         1.0      1            0  \n",
       "3  2011SR065311         1.0      1            0  \n",
       "4  2014SR054225         1.0      1            0  "
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#手动标注了500个sample\n",
    "labeled = em.read_csv_metadata('data/label+.csv', ltable=base_data, rtable=reg_data,\n",
    "                               fk_ltable='l_id', fk_rtable='r_id', key='_id')\n",
    "labeled.head()#Metadata file is not present in the given path; proceeding to read the csv file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Substep E: Traning machine learning algorithms\n",
    "split = em.split_train_test(labeled, train_proportion=0.70, random_state=0)#三七分训练集测试集\n",
    "train_data = split['train']\n",
    "test_data = split['test']\n",
    " \n",
    "dt = em.DTMatcher(name='DecisionTree', random_state=0)\n",
    "svm = em.SVMMatcher(name='SVM', random_state=0)\n",
    "rf = em.RFMatcher(name='RF', random_state=0)\n",
    "lg = em.LogRegMatcher(name='LogReg', random_state=0)\n",
    "ln = em.LinRegMatcher(name='LinReg')\n",
    "nb = em.NBMatcher(name='NaiveBayes')\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "attr_corres = em.get_attr_corres(base_data, reg_data)\n",
    "attr_corres['corres'] = [('company_id', 'company_id'), \n",
    "                         ('nor_names', 'nor_authors')]#指定两个数据集的列之间的对应关系\n",
    " \n",
    "l_attr_types = em.get_attr_types(base_data)#确定每一列的类型\n",
    "r_attr_types = em.get_attr_types(reg_data)\n",
    " \n",
    "tok = em.get_tokenizers_for_matching()#分词器这里默认是qgram分词\n",
    "sim = em.get_sim_funs_for_matching()#相似度函数\n",
    " \n",
    "F = em.get_features(base_data, reg_data, l_attr_types, r_attr_types, attr_corres, tok, sim)#特征提取的计算方式\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#F.to_csv('./data/F(modify+++).csv', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "0% [##############################] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:00\n",
      "C:\\Users\\Administrator\\AppData\\Roaming\\Python\\Python36\\site-packages\\sklearn\\utils\\deprecation.py:58: DeprecationWarning: Class Imputer is deprecated; Imputer was deprecated in version 0.20 and will be removed in 0.22. Import impute.SimpleImputer from sklearn instead.\n",
      "  warnings.warn(msg, category=DeprecationWarning)\n"
     ]
    }
   ],
   "source": [
    "#train_features = em.extract_feature_vecs(train_data, feature_table=F, attrs_after='label', show_progress=False) \n",
    "train_features = em.extract_feature_vecs(train_data, feature_table=F, attrs_after='label', show_progress=True) \n",
    "train_features = em.impute_table(train_features,  exclude_attrs=['_id', 'l_id', 'r_id', 'label'], strategy='mean')\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Matcher</th>\n",
       "      <th>Average precision</th>\n",
       "      <th>Average recall</th>\n",
       "      <th>Average f1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DecisionTree</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.987075</td>\n",
       "      <td>0.993424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>RF</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.991156</td>\n",
       "      <td>0.995529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SVM</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.980998</td>\n",
       "      <td>0.990383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LinReg</td>\n",
       "      <td>0.988571</td>\n",
       "      <td>0.981678</td>\n",
       "      <td>0.984805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LogReg</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.985760</td>\n",
       "      <td>0.992792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaiveBayes</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.987075</td>\n",
       "      <td>0.993424</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Matcher  Average precision  Average recall  Average f1\n",
       "0  DecisionTree           1.000000        0.987075    0.993424\n",
       "1            RF           1.000000        0.991156    0.995529\n",
       "2           SVM           1.000000        0.980998    0.990383\n",
       "3        LinReg           0.988571        0.981678    0.984805\n",
       "4        LogReg           1.000000        0.985760    0.992792\n",
       "5    NaiveBayes           1.000000        0.987075    0.993424"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = em.select_matcher([dt, rf, svm, ln, lg, nb], table=train_features, \n",
    "                           exclude_attrs=['_id', 'l_id', 'r_id', 'label'], k=5,\n",
    "                           target_attr='label',random_state=0)# metric='f1'\n",
    "result['cv_stats']\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Precision : 100.0% (77/77)\n",
      "Recall : 100.0% (77/77)\n",
      "F1 : 100.0%\n",
      "False positives : 0 (out of 77 positive predictions)\n",
      "False negatives : 0 (out of 75 negative predictions)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Roaming\\Python\\Python36\\site-packages\\sklearn\\utils\\deprecation.py:58: DeprecationWarning: Class Imputer is deprecated; Imputer was deprecated in version 0.20 and will be removed in 0.22. Import impute.SimpleImputer from sklearn instead.\n",
      "  warnings.warn(msg, category=DeprecationWarning)\n"
     ]
    }
   ],
   "source": [
    "best_model = result['selected_matcher']\n",
    "best_model.fit(table=train_features, exclude_attrs=['_id', 'l_id', 'r_id', 'label'], target_attr='label')\n",
    " \n",
    "test_features = em.extract_feature_vecs(test_data, feature_table=F, attrs_after='label', show_progress=False)\n",
    "test_features = em.impute_table(test_features, exclude_attrs=['_id', 'l_id', 'r_id', 'label'], strategy='mean')\n",
    " \n",
    "# Predict on the test data\n",
    "predictions = best_model.predict(table=test_features, exclude_attrs=['_id', 'l_id', 'r_id', 'label'], \n",
    "                                 append=True, target_attr='predicted', inplace=False)\n",
    "\n",
    "# Evaluate the predictions\n",
    "eval_result = em.eval_matches(predictions, 'label', 'predicted')\n",
    "em.print_eval_summary(eval_result)\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "0% [##############################] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:10\n",
      "C:\\Users\\Administrator\\AppData\\Roaming\\Python\\Python36\\site-packages\\sklearn\\utils\\deprecation.py:58: DeprecationWarning: Class Imputer is deprecated; Imputer was deprecated in version 0.20 and will be removed in 0.22. Import impute.SimpleImputer from sklearn instead.\n",
      "  warnings.warn(msg, category=DeprecationWarning)\n"
     ]
    }
   ],
   "source": [
    "candset_features = em.extract_feature_vecs(C1, feature_table=F, show_progress=True)\n",
    "candset_features = em.impute_table(candset_features, exclude_attrs=['_id', 'l_id', 'r_id'], strategy='mean')\n",
    "predictions = best_model.predict(table=candset_features, exclude_attrs=['_id', 'l_id', 'r_id'],\n",
    "                                 append=True, target_attr='predicted', inplace=False)\n",
    "matches = predictions[predictions.predicted == 1]\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5051, 16)"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matches.shape\n",
    "#matches.to_csv('./data/pre-matches(modify).csv', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>l_id</th>\n",
       "      <th>r_id</th>\n",
       "      <th>l_company_id</th>\n",
       "      <th>l_name</th>\n",
       "      <th>l_credit_code</th>\n",
       "      <th>r_company_id</th>\n",
       "      <th>r_author Nationality</th>\n",
       "      <th>r_fullname</th>\n",
       "      <th>r_regnum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>AUTOSCREEN拼接单元遥控器软件</td>\n",
       "      <td>2011SR044451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>RClink express网络抓屏软件</td>\n",
       "      <td>2011SR044453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>REALCOLOR虚屏软件</td>\n",
       "      <td>2011SR044389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>REALCOLOR集中管理软件</td>\n",
       "      <td>2011SR044449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>91110108754157151K</td>\n",
       "      <td>1172</td>\n",
       "      <td>北京真彩科创电子技术有限公司</td>\n",
       "      <td>WALLCONTROAL大屏拼接管理软件</td>\n",
       "      <td>2011SR044565</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _id  l_id  r_id l_company_id          l_name       l_credit_code  \\\n",
       "0    0     3     0         1172  北京真彩科创电子技术有限公司  91110108754157151K   \n",
       "1    1     3     1         1172  北京真彩科创电子技术有限公司  91110108754157151K   \n",
       "2    2     3     2         1172  北京真彩科创电子技术有限公司  91110108754157151K   \n",
       "3    3     3     3         1172  北京真彩科创电子技术有限公司  91110108754157151K   \n",
       "4    4     3     4         1172  北京真彩科创电子技术有限公司  91110108754157151K   \n",
       "\n",
       "  r_company_id r_author Nationality            r_fullname      r_regnum  \n",
       "0         1172       北京真彩科创电子技术有限公司   AUTOSCREEN拼接单元遥控器软件  2011SR044451  \n",
       "1         1172       北京真彩科创电子技术有限公司  RClink express网络抓屏软件  2011SR044453  \n",
       "2         1172       北京真彩科创电子技术有限公司         REALCOLOR虚屏软件  2011SR044389  \n",
       "3         1172       北京真彩科创电子技术有限公司       REALCOLOR集中管理软件  2011SR044449  \n",
       "4         1172       北京真彩科创电子技术有限公司  WALLCONTROAL大屏拼接管理软件  2011SR044565  "
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from py_entitymatching.catalog import catalog_manager as cm\n",
    "matches2 = matches[['_id', 'l_id', 'r_id', 'predicted']]\n",
    "matches2.reset_index(drop=True, inplace=True)\n",
    "cm.set_candset_properties(matches2, '_id', 'l_id', 'r_id', base_data, reg_data)\n",
    "matches2 = em.add_output_attributes(matches2, l_output_attrs=['company_id', 'name','credit_code'],\n",
    "                                   r_output_attrs=['company_id', 'author Nationality','fullname','regnum'],\n",
    "                                   l_output_prefix='l_', r_output_prefix='r_',\n",
    "                                   delete_from_catalog=False)\n",
    "matches2.drop('predicted', axis=1, inplace=True)\n",
    "matches2.head()\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5051, 8)"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matches2.drop('l_id', axis=1, inplace=True)\n",
    "matches2.drop('r_id', axis=1, inplace=True)\n",
    "matches2.to_csv('./data/matches(modify+).csv', encoding='utf-8')\n",
    "matches2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
